package com.gitee.neuray.security.wb.ribs.util;

import com.gitee.neuray.security.common.util.UUIDUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.Iterator;

public class PoiUtil {

    /**
     * 复制单元格
     * @param srcCell
     * @param distCell
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyCell(XSSFWorkbook wb, Cell srcCell, Cell distCell, boolean copyValueFlag) {
        CellStyle newStyle = wb.createCellStyle();
        CellStyle srcStyle = srcCell.getCellStyle();

        newStyle.cloneStyleFrom(srcStyle);
        newStyle.setFont(wb.getFontAt(srcStyle.getFontIndex()));

        // 样式
        distCell.setCellStyle(newStyle);

        // 内容
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }

        // 不同数据类型处理
        CellType srcCellType = srcCell.getCellTypeEnum();
        distCell.setCellType(srcCellType);

        if (copyValueFlag) {
            if (srcCellType == CellType.NUMERIC) {
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == CellType.STRING) {
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == CellType.BLANK) {

            } else if (srcCellType == CellType.BOOLEAN) {
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == CellType.ERROR) {
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == CellType.FORMULA) {
                distCell.setCellFormula(srcCell.getCellFormula());
            }
        }
    }

    /**
     * 行复制功能
     * @param wb 工作簿
     * @param fromRow 从哪行开始
     * @param toRow 目标行
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyRow(XSSFWorkbook wb, Row fromRow, Row toRow, boolean copyValueFlag) {
        toRow.setHeight(fromRow.getHeight());

        for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
            Cell tmpCell = cellIt.next();
            Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell, copyValueFlag);
        }

        Sheet worksheet = fromRow.getSheet();

        for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
            if (cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(),
                        (toRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                        cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
                worksheet.addMergedRegionUnsafe(newCellRangeAddress);
            }
        }
    }

    /**
     * excel 插入行 在开始行号之上新增
     * @param sheet
     * @param rows 插入几行
     * @param startRow 开始行号
     */
    public static void insertRow(Sheet sheet, int startRow, int rows) {
        sheet.shiftRows(startRow, sheet.getLastRowNum(), rows, true, false);
        for (int i = 0; i < rows; i++) {
            Row sourceRow = null;//原始位置
            Row targetRow = null;//移动后位置
            Cell sourceCell = null;
            Cell targetCell = null;
            sourceRow = sheet.createRow(startRow);
            targetRow = sheet.getRow(startRow + rows);
            sourceRow.setHeight(targetRow.getHeight());

            for (int m = targetRow.getFirstCellNum(); m < targetRow.getPhysicalNumberOfCells(); m++) {
                sourceCell = sourceRow.createCell(m);
                targetCell = targetRow.getCell(m);
                sourceCell.setCellStyle(targetCell.getCellStyle());
                sourceCell.setCellType(targetCell.getCellTypeEnum());
                //sourceCell.setCellFormula(targetCell.getCellFormula());
            }
            startRow++; 
        } 
 
    }

    public static void main(String[] args) throws IOException {

        File file = new File("C:\\Users\\wandLee\\Desktop\\报销\\报销单模板.xlsx");
        byte[] buffer = null;
        FileInputStream fis = new FileInputStream(file);
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        byte[] b = new byte[1024];
        int n;
        while ((n = fis.read(b)) != -1)
        {
            bos.write(b, 0, n);
        }
        fis.close();
        bos.close();
        buffer = bos.toByteArray();
        InputStream sbs = new ByteArrayInputStream(buffer);

        //读取excel模板
        XSSFWorkbook wb = new XSSFWorkbook(sbs);
        /**
         * 行复制功能
         * @param wb 工作簿
         * @param fromRow 从哪行开始
         * @param toRow 目标行
         * @param copyValueFlag true则连同cell的内容一起复制
         */
        Sheet sheet = wb.getSheetAt(4);
        //insertRow(sheet,8,1);
//        Row startrow = sheet.getRow(3);
//        Row ebdrow = sheet.getRow(4);
//        copyRow(wb,startrow,ebdrow,true);

        //wb.setForceFormulaRecalculation(true);

        sheet.getRow(7).getCell(4).setCellValue("22");
        sheet.getRow(8).getCell(4).setCellValue("22");
        XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
        System.out.println(sheet.getRow(14).getCell(4).getNumericCellValue());


        ByteArrayOutputStream boss=new ByteArrayOutputStream();
        wb.write(boss);
        byte[] barray=boss.toByteArray();
        InputStream is=new ByteArrayInputStream(barray);
        //todo 创建文件夹 临时文件
        //String savePath = request.getServletContext().getRealPath("/temp");
        String savePath = "D://temp";
        File tmpDir = new File(savePath);
        String fileWriteTime = UUIDUtils.generateShortUuid();
        // 判断上传文件的保存目录是否存在
        if (!tmpDir.exists() && !tmpDir.isDirectory())
        {
            //tmpDir.createNewFile();
            // 创建目录
            tmpDir.mkdir();
        }
        File fileTemp = new File(savePath + File.separator + "李万迪" + ".xlsx");
        FileOutputStream fileOutputStream = new FileOutputStream(fileTemp);

        int n1;
        while ((n1 = is.read(b)) != -1)
        {
            fileOutputStream.write(b, 0, n1);
        }

    }

}
